const express = require('express');

const routes = express.Router();

//引入mysql
const mysql = require('mysql');
//创建连接池
var pool = mysql.createPool({
    connectionLimit : 10,
    host : 'localhost',
    user : 'root',
    password : 'root',
    database : 'stumsg'
})

routes.route('/')
    .get( (req,resp)=>{

        //获取cookies
        var uid = req.cookies.uid;
        var gender = req.cookies.gender;
        //获取当前教师的专业id
        var sub_id = req.cookies.sub_id;
        //判断
        if(gender == 1){
            var pname = '教师';
        }else {
            var pname = '学生';

        }
        //看当前uid的最大值和最小值
        var sql = `SELECT  MIN(stu_list.uid) as min , MAX(stu_list.uid) as max  FROM stu_list`;
        pool.getConnection( (err , connection)=>{
            if(err) throw err;

            connection.query( sql , (error , result)=>{

                //执行成功  释放连接
                connection.release();
                var maxuid = result[0].max;
                var minuid = result[0].min;
                //console.log(maxuid)
                //console.log(minuid)

                resp.render('add_list',{
                    uid : uid,
                    pname : pname,
                    sub_id : sub_id,
                    maxuid : maxuid,
                    minuid : minuid
                })

                if(error) throw error;
            } )
        } )

    })

    .post( (req , resp)=>{
        //获取body标签提交过来的数据
        //学号
        var uid = req.body.uid.trim();
        //学生姓名
        var stu_name = req.body.stu_name.trim();
        //学生性别
        var gender = req.body.gender;
        //年龄
        //出生年月
        var birtmp = req.body.birtmp;
        //个人简介
        var intro = req.body.intro;
        //转换成时间戳
        var newTime = new Date(birtmp).getTime();
        //console.log(newTime)
        //获取当前的系统时间戳
        var dat = new Date().getTime();
        //console.log(dat)
        //提取当前时间戳的年份
        var year = new Date(dat).getFullYear();
        //console.log(year)
        //选择器的的年份
        var newtmp = new Date(newTime).getFullYear();
        //console.log(newtmp)
        //两个年份相减
        var age = parseInt(year - newtmp);
        //console.log(age)

        //所属专业
        var sub_id = req.body.sub_id;
        //学生自我介绍
        var txt = req.body.txt;

        //判断提交的数据有一项为空的话终止程序运行
        if( uid == '' || stu_name == '' || birtmp == ''){
            return false;
        }

        //录入学生信息
        var sql = `INSERT INTO stu_list ( uid , stu_name , pwd , age , gender , birtmp , sub_id , intro) VALUES ( ${uid} , '${stu_name}' , MD5(123456) , ${age} , ${gender} , ${newTime} , ${sub_id}, '${intro}' )`;
        //链接数据库
        pool.getConnection( function (err , connection){
            if(err) throw err;
            //    链接成功   执行sql语句
            connection.query( sql , (error , result)=>{

                //执行成功  释放连接
                connection.release();

                resp.redirect('/sel_list')

                if(error) throw error;
            } )
        } )
    } )

//暴露接口
module.exports = routes;